﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
using System.Data;

namespace Basefr.App.Helper
{
    /// <summary>
    /// sql帮助类：执行sql语句，操作数据库
    /// </summary>
    public class SqlHelper
    {
        private readonly string _connectionString;

        public SqlHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        #region 操作数据库
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="databaseName"></param>
        public void CreateDatabase(string databaseName)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"CREATE DATABASE {databaseName}", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 创建表,包含默认字段主键字段Id（guid类型）
        /// </summary>
        /// <param name="tableName"></param>
        public void CreateTable(string tableName)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"CREATE TABLE [{tableName}] (Id uniqueidentifier PRIMARY KEY)", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 创建表,包括字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        public void CreateTableAndFields(string tableName, string columns)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"CREATE TABLE {tableName} ({columns})", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        public void DropTable(string tableName)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"DROP TABLE {tableName}", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 新增字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">字段名</param>
        /// <param name="dataType">字段数据类型</param>
        public void AddColumn(string tableName, string columnName, string dataType)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"ALTER TABLE {tableName} ADD {columnName} {dataType}", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        public void DropColumn(string tableName, string columnName)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand($"ALTER TABLE {tableName} DROP COLUMN {columnName}", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    return command.ExecuteNonQuery();
                }
            }
        }
        #endregion
        #region 生成sql命令
        // 查询数据
        public static string BuildSelectStatement(string tableName, Dictionary<string, object> conditions = null, bool isLike=false)
        {
            var sql = new StringBuilder("SELECT * FROM " + tableName);

            if (conditions != null && conditions.Count > 0)
            {
                sql.Append(" WHERE ");
                var first = true;
                foreach (var condition in conditions)
                {
                    if (!first)
                    {
                        sql.Append(" AND ");
                    }
                    first = false;

                    sql.Append(condition.Key);

                    //判断是否有可支持模糊搜索的字段
                    if (isLike)
                    {
                        sql.Append(" LIKE @");
                        sql.Append(condition.Key);
                    }
                    else
                    {
                        sql.Append(" = @");
                        sql.Append(condition.Key);
                    }
                }
            }

            return sql.ToString();
        }

        // 新增数据
        public static string BuildInsertStatement(string tableName, Dictionary<string, object> columns)
        {
            var sql = new StringBuilder("INSERT INTO " + tableName + " (");
            var values = new StringBuilder("VALUES (");

            var first = true;
            foreach (var column in columns)
            {
                if (!first)
                {
                    sql.Append(", ");
                    values.Append(", ");
                }
                first = false;

                sql.Append(column.Key);
                values.Append("@");
                values.Append(column.Key);
            }

            sql.Append(") ");
            values.Append(")");

            sql.Append(values.ToString());

            return sql.ToString();
        }

        // 修改数据
        public static string BuildUpdateStatement(string tableName, Dictionary<string, object> setColumns, Dictionary<string, object> conditions)
        {
            var sql = new StringBuilder("UPDATE " + tableName + " SET ");

            var first = true;
            foreach (var column in setColumns)
            {
                if (!first)
                {
                    sql.Append(", ");
                }
                first = false;

                sql.Append(column.Key);
                sql.Append(" = @");
                sql.Append(column.Key);
            }

            if (conditions != null && conditions.Count > 0)
            {
                sql.Append(" WHERE ");
                first = true;
                foreach (var condition in conditions)
                {
                    if (!first)
                    {
                        sql.Append(" AND ");
                    }
                    first = false;

                    sql.Append(condition.Key);
                    sql.Append(" = @");
                    sql.Append(condition.Key);
                }
            }

            return sql.ToString();
        }

        // 删除数据
        public static string BuildDeleteStatement(string tableName, Dictionary<string, object> conditions)
        {
            var sql = new StringBuilder("DELETE FROM " + tableName);

            if (conditions != null && conditions.Count > 0)
            {
                sql.Append(" WHERE ");
                var first = true;
                foreach (var condition in conditions)
                {
                    if (!first)
                    {
                        sql.Append(" AND ");
                    }
                    first = false;

                    sql.Append(condition.Key);
                    sql.Append(" = @");
                    sql.Append(condition.Key);
                }
            }

            return sql.ToString();
        }
        #endregion
        #region 执行增删改查
        /// <summary>
        /// 查询数据，返回List数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conditions">查询条件</param>
        /// <returns></returns>
        public List<Dictionary<string, object>> QueryData(string tableName, Dictionary<string, object> conditions = null, bool isLike=false)
        {
            var sql = BuildSelectStatement(tableName, conditions, isLike);
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    ConfigureCommandParameters(command, conditions);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        var data = new List<Dictionary<string, object>>();
                        while (reader.Read())
                        {
                            var row = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                row.Add(reader.GetName(i), reader[i]);
                            }
                            data.Add(row);
                        }
                        return data;
                    }
                }
            }
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">新增的数据</param>
        /// <returns></returns>
        public int InsertData(string tableName, Dictionary<string, object> columns)
        {
            var sql = BuildInsertStatement(tableName, columns);
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    ConfigureCommandParameters(command, columns);
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setColumns">待更新的数据</param>
        /// <param name="conditions">更新数据条件</param>
        /// <returns></returns>
        public int UpdateData(string tableName, Dictionary<string, object> setColumns, Dictionary<string, object> conditions)
        {
            var sql = BuildUpdateStatement(tableName, setColumns, conditions);
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    ConfigureCommandParameters(command, CombineDictionaries(setColumns, conditions));
                    //ConfigureCommandParameters(command, setColumns);
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conditions">待删除的数据信息</param>
        /// <returns></returns>
        public int DeleteData(string tableName, Dictionary<string, object> conditions)
        {
            var sql = BuildDeleteStatement(tableName, conditions);
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    ConfigureCommandParameters(command, conditions);
                    return command.ExecuteNonQuery();
                }
            }
        }

        // 配置SQL命令的参数
        private void ConfigureCommandParameters(SqlCommand command, Dictionary<string, object> parameters)
        {
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                }
            }
        }

        // 合并两个字典
        private Dictionary<string, object> CombineDictionaries(Dictionary<string, object> first, Dictionary<string, object> second)
        {
            var combined = new Dictionary<string, object>(first);
            foreach (var pair in second)
            {
                combined[pair.Key] = pair.Value;
            }
            return combined;
        }
        #endregion

        #region 执行非查询命令（增删改）

        public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters = null)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    ConfigureCommandParameters(command, parameters);
                    return command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region 执行查询命令（查）返回DataTable数据

        public DataTable ExecuteQuery(string sql, Dictionary<string, object> parameters = null)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    ConfigureCommandParameters(command, parameters);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
            }
        }

        #endregion
    }
}
